import pymysql
import datetime
import openpyxl as vb
import time
import getpass
import os

def sql_to_excel(ip_address):   
  
  #1.获取当前日期
  now=time.strftime('%y/%m/%d %H:%M')
  today=datetime.datetime.now().strftime('%m%d')
  tomorrow=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%m%d')
  todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
  tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d') 
  pre_date=(datetime.datetime.now()+datetime.timedelta(days=-2)).strftime('%Y-%m-%d') #邮件查询图表定义历史天数

  #2.建立mysql的连接
  conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
            password='123456',port=3306,charset='utf8')
  # 获得游标
  cursor = conn.cursor()

 
  cursor.execute('''update assignment set Test_nameid = (select test_nameid from testlist where testlist.test_name 
  like concat('%',(assignment.test_name),'%' )and testlist.Test_day=assignment.Left_day) where assignment.test_name!='' and assignment.Date>='2024-04-10' ''')  

  conn.commit()
  print('D-1: 开始把分活儿结果写入Excel文件......')

 
  user_name = getpass.getuser()    
  filepath = r'C:\自动分活儿\Reliability Test plan.xlsx'
   
  serverfolder_tcc = r'\\10.114.184.9\Tcc_Rel\04 Task_Assignment\备份\分活后备份'
  filepath_after_assignment_server_tcc = r'\\10.114.184.9\Tcc_Rel\04 Task_Assignment\备份\分活后备份\Reliability Test plan '+today+'分活儿后by '+user_name+'.xlsx'
  workbook = vb.load_workbook(filepath)
  
 
 
 #格式设置
  fontstyle0 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文兰色  
  fontstyle_red = vb.styles.Font(name='Arial',size=10,color='DC143C')  #正文红色 
  cellbackground0 = vb.styles.PatternFill(fill_type='gray125') 
  #取出日期为明日的已分实验，用operator非空，或者是s实验,或是cy实验（可能没换成人）或者是没换成人的c实验来判定； 取出日期为今日的a实验，指那些当日完成的a 实验。y实验未指定人，也更新了状态需要输出到Excel
  cursor.execute('''select Test_no, StartDate, ComDate, Special,Operator,Status,date,Left_day,Test_day,Total_day,Test_hour from assignment
   where ((Special = 's' or Special = 'c' or Special = 'y' or Special = 'cy' or operator != '') and date = %s) or (Special = 'a' and date = %s )''', (tomorrowcolumn,todaycolumn))   
  n = 0 #记录要更新的行数
  sheetnames = ['ALT','DVT','Accessory','Other']
  allrows=cursor.fetchall() 
  for row_db in allrows:  
    #先把值算出来，避免下面再重复计算，提升效率
    rowdb0=row_db[0] 
    rowdb1=row_db[1]   
    rowdb2=row_db[2] 
    rowdb3=row_db[3] 
    rowdb4=row_db[4] 
    rowdb5=row_db[5] 
    rowdb6=row_db[6] 
    rowdb7=row_db[7] 
    rowdb8=row_db[8] 
    rowdb9=row_db[9]
    rowdb10=row_db[10]  

    for name in sheetnames:
      worksheet = workbook[name]    
      maxrows=worksheet.max_row
      # maxcols=worksheet.max_column  #20240412更新不用此变量，而是指定了循环的列范围，以节约时间
      #针对DVT sheet， 只比对最后1000行数据，节约时间
      if name=='DVT' and maxrows-1100>=1:
        initialrows=maxrows-1100
      elif name=='ALT' and maxrows-80>=1:
        initialrows=maxrows-80
      elif name=='Accessory' and maxrows-50>=1:
        initialrows=maxrows-50
      elif name=='Other' and maxrows-40>=1:
        initialrows=maxrows-40
      else:
        initialrows=1      
      sheetzone=worksheet.iter_rows(min_row=initialrows,max_row=maxrows,min_col=1,max_col=23) # 逐行
      # else:
      #   sheetzone=worksheet.rows
      for row_excel in sheetzone:
        # print(row_excel[2].value,type(row_excel[2].value),row_db[0],type(row_db[0]))
        if str(row_excel[2].value) == rowdb0: #Test_no   找实验号相同的
          # print('*判定',row_excel[2].value,row_db[0])
          row_excel[8].value = rowdb1 #StartDate
          row_excel[8].fill = cellbackground0
          row_excel[9].value = rowdb2 #ComDate     
          row_excel[9].fill = cellbackground0
          row_excel[10].value = rowdb10 #test_hour
          row_excel[10].fill = cellbackground0
          row_excel[14].value = rowdb4 #Operator
          row_excel[14].fill = cellbackground0
          row_excel[14].font = fontstyle0
          

          if row_db[3] == 'cy':
            row_excel[12].value = rowdb3 #Special 只有CY才写入
            row_excel[12].font = fontstyle_red
            row_excel[12].fill = cellbackground0                       
            row_excel[14].font = fontstyle_red
            worksheet.column_dimensions['O'].width = 10            


          row_excel[15].value = rowdb5 #Status
          row_excel[15].fill = cellbackground0
          row_excel[18].value = str(rowdb6)  #Date      
                  
          row_excel[19].value = rowdb7  #Left_day
          
          row_excel[20].value = rowdb8  #Test_day
          
            
          row_excel[21].value = rowdb9  #Total_day
          # if row_excel[12].value=='a':
          #   print('*',row_excel[2].value,'Date',str(row_db[6]),'test_day:',row_db[8],'comda:',rowdb2,row_db[2])
            
          n=n+1       
      for col in ['S','T','U','V']:
        worksheet.column_dimensions[col].hidden = True   #每个sheet都要执行一次      
        # worksheet.column_dimensions[col].hidden = False   #每个sheet都要执行一次

      #格式化Left day及后面两列格式为整数（20240429添加）
      column = worksheet['T']
      for cell in column:
        cell.number_format = '0'
      column = worksheet['U']
      for cell in column:
        cell.number_format = '0'
      column = worksheet['V']
      for cell in column:
        cell.number_format = '0'        
      column = worksheet['J']
      for cell in column:
        cell.number_format = 'mm/dd'
  workbook.save(filepath)
 

  if os.path.exists(serverfolder_tcc):  

    workbook = vb.load_workbook(filepath)
    workbook.save(filepath_after_assignment_server_tcc)  
    print('备份于tcc server：\\10.114.184.9\Tcc_Rel\04 Task_Assignment\备份\分活后备份')

  print('D-2: 更新Reliability Test plan 表格成功！ ', n, '行数据已更新！')

 
  pathfile_todayhours = r'C:\自动分活儿\TCC每日工时核对.xlsx'
  # pathfile_todayhours_server = r'\\10.114.183.55\1011418355D-Sharefolder\自动分活儿备份\TCC每日工时核对.xlsx'
  # pathfile_todayhours_server_tcc = r'\\10.114.184.9\Tcc_Rel\04 Task_Assignment\备份\分活后备份\TCC每日工时核对.xlsx'
  workbook = vb.load_workbook(pathfile_todayhours)
  allsheets = workbook.worksheets
  sheetname = today+' 实际工时'
  for i in allsheets:
    if i.title == sheetname:      
      worksheet = workbook[sheetname]
      workbook.remove(worksheet)
  workbook.save(pathfile_todayhours)
  fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
  fontstyle1 = vb.styles.Font(name='Arial',size=10)    #正文
  fontstyle2 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文红色
  alignstyle = vb.styles.Alignment(horizontal='center',vertical='center')  #居中
  cellbackground= vb.styles.PatternFill(fill_type='lightGray') 

  workbook = vb.load_workbook(pathfile_todayhours)
  workbook.create_sheet(sheetname)
  worksheet = workbook[sheetname]
  worksheet['A1'] = '姓名'  
  worksheet['B1'] = '实际工时'  
  worksheet['C1'] = '工作量'   #新增
  worksheet['E1'] = '统计时间：'+now +'  注：实际工时-->当日承担实验的标准工时*系数的汇总；工作量-->为标准工时*系数*难度系数后的汇总'
  for cell in ['A1','B1','C1']:
    worksheet[cell].fill = cellbackground
    worksheet[cell].alignment = alignstyle

  #--------下面为2021年3月11日添加的独立内容：为了使得工作量、工时同时在一个柱状图中展示，并且按照工作量的大小顺序展示。
  name_list_rankbyworkload =[]  
  total_workload_byname =[]
  total_workhours_byname = []
  #下面20240411增加了a.special!='s',0420恢复，s是第二天不算工时，当天还是要算
  cursor.execute('''select operator, sum(a.test_hour*a.Coefficient*t.Diff_score),sum(a.test_hour*a.Coefficient) from 
  assignment as a LEFT JOIN testlist as t on t.test_nameid = a.test_nameid and t.Test_day=1 where a.operator !='' and
  a.test_hour !='Null' and a.date = %s group by operator order by sum(a.test_hour*a.Coefficient*t.Diff_score) desc''',todaycolumn)

  name_load_hours=cursor.fetchall()
  # for n in name_load_hours:
  #   name_list_rankbyworkload.append(n[0])
  #   total_workload_byname.append(n[1])
  #   total_workhours_byname.append(n[2])
#-----------------------------------------------------
  # cursor.execute('''select operator, sum(test_hour*coefficient) from assignment where operator != ''and test_hour !='Null' 
  # and date = %s group by Operator order by sum(test_hour*coefficient) desc''', todaycolumn) 
  # #将获取到的sql数据全部显示出来
  # allrows=cursor.fetchall()  
  

  n=len(name_load_hours)
  
  for row in range(2,n+2):           
    worksheet.cell(row,1,value = name_load_hours[row-2][0])
    worksheet.cell(row,2,value = name_load_hours[row-2][2]).font = fontstyle2
    worksheet.cell(row,2).alignment = alignstyle
    worksheet.cell(row,3,value = name_load_hours[row-2][1]).font = fontstyle2
    worksheet.cell(row,3).alignment = alignstyle

  worksheet['A1'].font = fontstyle
  worksheet['A1'].alignment =alignstyle
  worksheet['B1'].font = fontstyle
  worksheet['B1'].alignment =alignstyle
  worksheet['C1'].font = fontstyle
  worksheet['C1'].alignment =alignstyle
  
  worksheet.row_dimensions[1].height =20

  workbook.active=worksheet

  workbook.save(pathfile_todayhours)
  # if os.path.exists(serverfolder): 
  #   workbook.save(pathfile_todayhours_server)
  # if os.path.exists(serverfolder_tcc): 
  #   workbook.save(pathfile_todayhours_server_tcc)
  

  print('D-3: 更新TCC每日工时核对表格成功！ ')

  #-------V2.0更新OPENPYXL：写明日工时及加班建议表格
  pathfile_tasks = r'C:\自动分活儿\TCC分活儿表.xlsx'
  # pathfile_tasks_server = r'\\10.114.183.55\1011418355D-Sharefolder\自动分活儿备份\TCC分活儿表.xlsx'
  # pathfile_tasks_server_tcc = r'\\10.114.184.9\Tcc_Rel\04 Task_Assignment\备份\分活后备份\TCC分活儿表.xlsx'
  workbook = vb.load_workbook(pathfile_tasks)
  allsheets = workbook.worksheets
  sheetname1 = tomorrow+' 预估工时'
  sheetname2 = tomorrow

  for i in allsheets:
    if i.title == sheetname1:      
      worksheet1 = workbook[sheetname1]
      workbook.remove(worksheet1)
    if i.title == sheetname2:      
      worksheet2 = workbook[sheetname2]
      workbook.remove(worksheet2)
  workbook.save(pathfile_tasks)

  fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
  fontstyle1 = vb.styles.Font(name='Arial',bold=True,size=10)    #标题
  fontstyle2 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文兰色
  fontstyle3 = vb.styles.Font(name='Arial',size=10,color='DC143C')  #正文红色
  alignstyle = vb.styles.Alignment(horizontal='center',vertical='center')  #居中
  cellbackground= vb.styles.PatternFill(fill_type='lightGray') 

  workbook = vb.load_workbook(pathfile_tasks)
  workbook.create_sheet(sheetname1)
  worksheet1 = workbook[sheetname1]
  worksheet1['A1'] = 'Employee_id'  
  worksheet1['B1'] = 'Employee_name'  
  worksheet1['C1'] = 'Estimatedhour'
  worksheet1['D1'] = 'Workload'
  worksheet1['E1'] = 'Spare_hours'
  worksheet1['F1'] = 'OT'
  worksheet1['K1'] = '工时校验'

  
  cursor.execute('select count(*) from namelist')
  b=cursor.fetchone()  
  cursor.execute('select * from namelist')
  allrows=cursor.fetchall()  
  for row in range(2,b[0]+2):      
    worksheet1.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
    worksheet1.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle       
    worksheet1.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle 
    worksheet1.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle 
    worksheet1.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle 
    worksheet1.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle 
    worksheet1.cell(row,6).font = fontstyle2  
    #读取数据库assignmnet中的根据实际实验统计的明日预估工时
    cursor.execute('''select sum(Test_hour*Coefficient) from assignment where date=(select max(date)from assignment)and Operator=%s''',allrows[row-2][1])
    actual_hours=cursor.fetchone()      
    if actual_hours[0] is None:      
      a_hours=0
    else:
      a_hours=round(actual_hours[0],1)
    worksheet1.cell(row,11,value = a_hours).alignment = alignstyle 
    if a_hours!=allrows[row-2][3]:      
      print(allrows[row-2][3],a_hours)
      worksheet1.cell(row,11).font = fontstyle3
      worksheet1.cell(row,11).fill = cellbackground
    


  for cell in ['A1','B1','C1','D1','E1','F1']:    
    worksheet1[cell].fill = cellbackground
    worksheet1[cell].font = fontstyle1
    worksheet1[cell].alignment = alignstyle

  worksheet1['F1'].alignment = alignstyle 

  worksheet1.column_dimensions['A'].width = 12
  worksheet1.column_dimensions['B'].width = 15
  worksheet1.column_dimensions['C'].width = 14
  worksheet1.column_dimensions['D'].width = 11
  worksheet1.column_dimensions['E'].width = 12
  worksheet1.column_dimensions['F'].width = 11
  worksheet1.column_dimensions['K'].width = 12

  worksheet1.row_dimensions[1].height =20
  
  workbook.save(pathfile_tasks)


  #5.写第3个表格：分活表（以明天日期命名）
   #-------V2.0更新OPENPYXL：写分活儿表  
  workbook = vb.load_workbook(pathfile_tasks)
  workbook.create_sheet(sheetname2)
  worksheet2 = workbook[sheetname2]

  fontstyle = vb.styles.Font(name='Times New Roman',bold=True,size=10)  #标题
  fontstyle1 = vb.styles.Font(name='Arial',bold=True,size=10)    #标题
  fontstyle2 = vb.styles.Font(name='Arial',size=10,color='0000FF')  #正文蓝色
  alignstyle = vb.styles.Alignment(horizontal='center',vertical='center')  #居中  
  alignstyle1 = vb.styles.Alignment(horizontal='left',vertical='center')  #左 
  cellbackground= vb.styles.PatternFill(fill_type='lightGray') 

  worksheet2['A1'] = 'Product'  
  worksheet2['B1'] = 'Test_no'  
  worksheet2['C1'] = 'Qty'
  worksheet2['D1'] = 'Vintage'
  worksheet2['E1'] = 'Test_category'
  worksheet2['F1'] = 'Test_name'
  worksheet2['G1'] = 'Status'  
  worksheet2['H1'] = 'Test_hour'  
  worksheet2['I1'] = 'Coefficient'
  worksheet2['J1'] = 'Special'
  worksheet2['K1'] = 'Total_hours'
  worksheet2['L1'] = 'Operator'
  worksheet2['M1'] = 'Lab_Engineer'
  worksheet2['N1'] = 'Comments'

  for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1','N1']:
    worksheet2[cell].fill = cellbackground
    worksheet2[cell].alignment = alignstyle

  #获取数据库assignment的行数a[0]。 
  cursor.execute('''select count(*) from assignment where date=(select max(date)from assignment) and (Operator!='' or Special !='')''')
  a=cursor.fetchone() 
  #读取数据库assignmnet所有内容(未分派实验不读)
  cursor.execute('''select Product, Test_no, Qty, Vintage, Test_category,Test_name,Status,Test_hour,Coefficient,Special,Operator,
  Lab_engineer, Comments from assignment where date=(select max(date)from assignment)and (Operator!='' or Special !='')''')
  allrows=cursor.fetchall()  
  for row in range(2,a[0]+2):          
    worksheet2.cell(row,1,value = allrows[row-2][0]).alignment = alignstyle 
    worksheet2.cell(row,1).alignment = alignstyle1 
    worksheet2.cell(row,2,value = allrows[row-2][1]).alignment = alignstyle 
    worksheet2.cell(row,2).alignment = alignstyle1 
    worksheet2.cell(row,3,value = allrows[row-2][2]).alignment = alignstyle     
    worksheet2.cell(row,4,value = allrows[row-2][3]).alignment = alignstyle
    worksheet2.cell(row,4).alignment = alignstyle1  
    worksheet2.cell(row,5,value = allrows[row-2][4]).alignment = alignstyle 
    worksheet2.cell(row,5).alignment = alignstyle1 
    worksheet2.cell(row,6,value = allrows[row-2][5]).alignment = alignstyle 
    worksheet2.cell(row,7,value = allrows[row-2][6]).alignment = alignstyle 
    worksheet2.cell(row,8,value = allrows[row-2][7]).alignment = alignstyle 
    worksheet2.cell(row,9,value = allrows[row-2][8]).alignment = alignstyle 
    worksheet2.cell(row,10,value = allrows[row-2][9]).alignment = alignstyle 
    total_hours=round(float(allrows[row-2][7])*float(allrows[row-2][8]),1)  #round保留小数点后1位，四舍五入
    worksheet2.cell(row,11,value =total_hours).alignment = alignstyle 
    worksheet2.cell(row,12,value = allrows[row-2][10]).alignment = alignstyle 
    worksheet2.cell(row,12).font = fontstyle2
    worksheet2.cell(row,13,value = allrows[row-2][11]).alignment = alignstyle 
    worksheet2.cell(row,14,value = allrows[row-2][12]).alignment = alignstyle 
    worksheet2.cell(row,14).alignment = alignstyle1 


 
  for cell in ['A1','B1','C1','D1','E1','F1','G1','H1','I1','J1','K1','L1','M1']:
    worksheet2[cell].font=fontstyle1 

  
 

  worksheet2.column_dimensions['A'].width = 12
  worksheet2.column_dimensions['B'].width = 11
  worksheet2.column_dimensions['C'].width = 8
  worksheet2.column_dimensions['D'].width = 10
  worksheet2.column_dimensions['E'].width = 13
  worksheet2.column_dimensions['F'].width = 12
  worksheet2.column_dimensions['H'].width = 12
  worksheet2.column_dimensions['I'].width = 12
  worksheet2.column_dimensions['J'].width = 12
  worksheet2.column_dimensions['K'].width = 12
  worksheet2.column_dimensions['L'].width = 12
  worksheet2.column_dimensions['M'].width = 12
  worksheet2.column_dimensions['N'].width = 200

  worksheet2.row_dimensions[1].height =20
  worksheet2.auto_filter.ref = "A1:N1"

  workbook.active=worksheet2
  workbook.save(pathfile_tasks)
  # if os.path.exists(serverfolder):     
  #   workbook.save(pathfile_tasks_server)
  # if os.path.exists(serverfolder_tcc):     
  #   workbook.save(pathfile_tasks_server_tcc)
  print('D-4: 更新TCC分活儿表成功！ ')

    #20200808增加：更新Inprocess表格  20201211确认保留：用来统计未来工时，全部读出来，不管今日分下去或者没分的实验
  cursor.execute('''delete from inprocess where date=%s''',tomorrowcolumn) 
  conn.commit()
  # print('D4-T1')
  cursor.execute('''insert into inprocess select * from assignment where date=(select max(date)from assignment)''') 
  conn.commit()
  # print('D4-T2')

  #20201218增加：新增plan_all_tests 表格，用来排未来计划。先清空再复制inprocess内容进去，初始日期为明日。基于明日分活儿结果续排。
  cursor.execute('delete from plan_all_tests') 
  conn.commit()
  # print('D4-T3')
  cursor.execute('''insert into plan_all_tests select * from inprocess where date=(select max(date)from inprocess)''') 
  conn.commit()

  print('D-5: 更新数据库inprocess数据成功，用于多日分活儿！ ')

  #7.释放游标，断开数据库，释放资源
  cursor.close()
  conn.close()
  time_end = datetime.datetime.now() #程序计时终点
  
  

  # output=(' 分活儿完成！ 数据已写入Excel！总耗时%s秒! 稍候进行图形显示！' % totoal_t)
  # output=(' 分活儿完成！总耗时%s秒! ' % totoal_t)
  output=(' 实验分派完成，正在生成分活儿报告汇总! ')
  # time.sleep(3)
  print('D-6: 分活儿已完成，开始生成报告汇总，各种图形及准备邮件发送！ ')
  print('--------------------------------------------------------------------')
  return(output)  


if __name__=="__main__":    
  # ip_address='localhost'
  ip_address='10.114.183.55'
  sql_to_excel(ip_address)
 